{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "plt.rcParams['font.sans-serif'] = 'SimHei'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Legend\\AppData\\Local\\Programs\\Python\\Python39\\lib\\site-packages\\openpyxl\\styles\\stylesheet.py:226: UserWarning: Workbook contains no default style, apply openpyxl's default\n",
      "  warn(\"Workbook contains no default style, apply openpyxl's default\")\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User ID</th>\n",
       "      <th>Date</th>\n",
       "      <th>sales_count</th>\n",
       "      <th>sales_amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>19970101.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>11.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.0</td>\n",
       "      <td>19970112.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>12.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2.0</td>\n",
       "      <td>19970112.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>77.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3.0</td>\n",
       "      <td>19970102.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>20.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3.0</td>\n",
       "      <td>19970330.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>20.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69654</th>\n",
       "      <td>23568.0</td>\n",
       "      <td>19970405.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>83.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69655</th>\n",
       "      <td>23568.0</td>\n",
       "      <td>19970422.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>14.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69656</th>\n",
       "      <td>23569.0</td>\n",
       "      <td>19970325.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>25.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69657</th>\n",
       "      <td>23570.0</td>\n",
       "      <td>19970325.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>51.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69658</th>\n",
       "      <td>23570.0</td>\n",
       "      <td>19970326.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>42.96</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>69659 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       User ID        Date  sales_count  sales_amount\n",
       "0          1.0  19970101.0          1.0         11.77\n",
       "1          2.0  19970112.0          1.0         12.00\n",
       "2          2.0  19970112.0          5.0         77.00\n",
       "3          3.0  19970102.0          2.0         20.76\n",
       "4          3.0  19970330.0          2.0         20.76\n",
       "...        ...         ...          ...           ...\n",
       "69654  23568.0  19970405.0          4.0         83.74\n",
       "69655  23568.0  19970422.0          1.0         14.99\n",
       "69656  23569.0  19970325.0          2.0         25.74\n",
       "69657  23570.0  19970325.0          3.0         51.12\n",
       "69658  23570.0  19970326.0          2.0         42.96\n",
       "\n",
       "[69659 rows x 4 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.read_excel('musicSales.xlsx')\n",
    "\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 69659 entries, 0 to 69658\n",
      "Data columns (total 4 columns):\n",
      " #   Column        Non-Null Count  Dtype  \n",
      "---  ------        --------------  -----  \n",
      " 0   User ID       69659 non-null  int64  \n",
      " 1   Date          69659 non-null  float64\n",
      " 2   sales_count   69659 non-null  int64  \n",
      " 3   sales_amount  69659 non-null  float64\n",
      "dtypes: float64(2), int64(2)\n",
      "memory usage: 2.1 MB\n"
     ]
    }
   ],
   "source": [
    "def toInt(this):\n",
    "    return int(this)\n",
    "\n",
    "data['User ID'] = data['User ID'].apply(toInt)\n",
    "data['sales_count'] = data['sales_count'].apply(toInt)\n",
    "\n",
    "data.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# R\n",
    "计算每个ID最近活跃时间，当前时间为1998-07-01"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User ID</th>\n",
       "      <th>Date</th>\n",
       "      <th>sales_count</th>\n",
       "      <th>sales_amount</th>\n",
       "      <th>Day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>65849</th>\n",
       "      <td>22238</td>\n",
       "      <td>19970817.0</td>\n",
       "      <td>1</td>\n",
       "      <td>13.97</td>\n",
       "      <td>1997-08-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37916</th>\n",
       "      <td>12458</td>\n",
       "      <td>19970214.0</td>\n",
       "      <td>1</td>\n",
       "      <td>26.99</td>\n",
       "      <td>1997-02-14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15123</th>\n",
       "      <td>4776</td>\n",
       "      <td>19980101.0</td>\n",
       "      <td>6</td>\n",
       "      <td>84.94</td>\n",
       "      <td>1998-01-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       User ID        Date  sales_count  sales_amount         Day\n",
       "65849    22238  19970817.0            1         13.97  1997-08-17\n",
       "37916    12458  19970214.0            1         26.99  1997-02-14\n",
       "15123     4776  19980101.0            6         84.94  1998-01-01"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from datetime import datetime\n",
    "import time\n",
    "\n",
    "# 更改时间格式为 年-月-日\n",
    "def to_day(Date):\n",
    "    Date = str(int(Date))\n",
    "    #转换成时间数组\n",
    "    timeArray = time.strptime(Date, '%Y%m%d')\n",
    "    # 把时间数组转换成新格式\n",
    "    dt_new = time.strftime(\"%Y-%m-%d\",timeArray)\n",
    "    return dt_new\n",
    "\n",
    "data['Day'] = data['Date'].apply(to_day)\n",
    "\n",
    "data.sample(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User ID</th>\n",
       "      <th>Day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1997-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1997-01-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>1998-05-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>1997-12-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>1998-01-03</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   User ID        Day\n",
       "0        1 1997-01-01\n",
       "1        2 1997-01-12\n",
       "2        3 1998-05-28\n",
       "3        4 1997-12-12\n",
       "4        5 1998-01-03"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# recentDay是每个客户的最后一次购买信息\n",
    "r = data.groupby(['User ID'])[['Day']].max().reset_index()\n",
    "\n",
    "# 把object转化为datetime\n",
    "def toDateTime(object):\n",
    "    datetime = pd.to_datetime(object)\n",
    "    return datetime\n",
    "\n",
    "r['Day'] = r['Day'].apply(toDateTime)\n",
    "\n",
    "r.head()\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User ID</th>\n",
       "      <th>Day</th>\n",
       "      <th>recentDay</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1997-01-01</td>\n",
       "      <td>546</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1997-01-12</td>\n",
       "      <td>535</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>1998-05-28</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>1997-12-12</td>\n",
       "      <td>201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>1998-01-03</td>\n",
       "      <td>179</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23565</th>\n",
       "      <td>23566</td>\n",
       "      <td>1997-03-25</td>\n",
       "      <td>463</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23566</th>\n",
       "      <td>23567</td>\n",
       "      <td>1997-03-25</td>\n",
       "      <td>463</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23567</th>\n",
       "      <td>23568</td>\n",
       "      <td>1997-04-22</td>\n",
       "      <td>435</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23568</th>\n",
       "      <td>23569</td>\n",
       "      <td>1997-03-25</td>\n",
       "      <td>463</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23569</th>\n",
       "      <td>23570</td>\n",
       "      <td>1997-03-26</td>\n",
       "      <td>462</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>23570 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       User ID        Day  recentDay\n",
       "0            1 1997-01-01        546\n",
       "1            2 1997-01-12        535\n",
       "2            3 1998-05-28         34\n",
       "3            4 1997-12-12        201\n",
       "4            5 1998-01-03        179\n",
       "...        ...        ...        ...\n",
       "23565    23566 1997-03-25        463\n",
       "23566    23567 1997-03-25        463\n",
       "23567    23568 1997-04-22        435\n",
       "23568    23569 1997-03-25        463\n",
       "23569    23570 1997-03-26        462\n",
       "\n",
       "[23570 rows x 3 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#计算R值\n",
    "r['recentDay'] = (pd.to_datetime('1998-07-01')-r['Day']).dt.days\n",
    "\n",
    "r"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# F\n",
    "计算每个用户的购买次数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User ID</th>\n",
       "      <th>Date</th>\n",
       "      <th>sales_count</th>\n",
       "      <th>sales_amount</th>\n",
       "      <th>Day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>19970101.0</td>\n",
       "      <td>1</td>\n",
       "      <td>11.77</td>\n",
       "      <td>1997-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>19970112.0</td>\n",
       "      <td>1</td>\n",
       "      <td>12.00</td>\n",
       "      <td>1997-01-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>19970112.0</td>\n",
       "      <td>5</td>\n",
       "      <td>77.00</td>\n",
       "      <td>1997-01-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>19970102.0</td>\n",
       "      <td>2</td>\n",
       "      <td>20.76</td>\n",
       "      <td>1997-01-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3</td>\n",
       "      <td>19970330.0</td>\n",
       "      <td>2</td>\n",
       "      <td>20.76</td>\n",
       "      <td>1997-03-30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   User ID        Date  sales_count  sales_amount         Day\n",
       "0        1  19970101.0            1         11.77  1997-01-01\n",
       "1        2  19970112.0            1         12.00  1997-01-12\n",
       "2        2  19970112.0            5         77.00  1997-01-12\n",
       "3        3  19970102.0            2         20.76  1997-01-02\n",
       "4        3  19970330.0            2         20.76  1997-03-30"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User ID</th>\n",
       "      <th>tradeTimes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23565</th>\n",
       "      <td>23566</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23566</th>\n",
       "      <td>23567</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23567</th>\n",
       "      <td>23568</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23568</th>\n",
       "      <td>23569</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23569</th>\n",
       "      <td>23570</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>23570 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       User ID  tradeTimes\n",
       "0            1           1\n",
       "1            2           2\n",
       "2            3           6\n",
       "3            4           4\n",
       "4            5          11\n",
       "...        ...         ...\n",
       "23565    23566           1\n",
       "23566    23567           1\n",
       "23567    23568           3\n",
       "23568    23569           1\n",
       "23569    23570           2\n",
       "\n",
       "[23570 rows x 2 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "f = data.groupby(['User ID','sales_count','sales_amount']).size().reset_index(name=\"tradeTimes\")\n",
    "\n",
    "f = f.groupby(['User ID'])[['tradeTimes']].sum().reset_index()\n",
    "\n",
    "f"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 23570 entries, 0 to 23569\n",
      "Data columns (total 2 columns):\n",
      " #   Column      Non-Null Count  Dtype\n",
      "---  ------      --------------  -----\n",
      " 0   User ID     23570 non-null  int64\n",
      " 1   tradeTimes  23570 non-null  int64\n",
      "dtypes: int64(2)\n",
      "memory usage: 368.4 KB\n"
     ]
    }
   ],
   "source": [
    "f.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# M \n",
    "每个客户平均购买金额，也可以是累计购买金额。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User ID</th>\n",
       "      <th>Date</th>\n",
       "      <th>sales_count</th>\n",
       "      <th>sales_amount</th>\n",
       "      <th>Day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>19970101.0</td>\n",
       "      <td>1</td>\n",
       "      <td>11.77</td>\n",
       "      <td>1997-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>19970112.0</td>\n",
       "      <td>1</td>\n",
       "      <td>12.00</td>\n",
       "      <td>1997-01-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>19970112.0</td>\n",
       "      <td>5</td>\n",
       "      <td>77.00</td>\n",
       "      <td>1997-01-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>19970102.0</td>\n",
       "      <td>2</td>\n",
       "      <td>20.76</td>\n",
       "      <td>1997-01-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3</td>\n",
       "      <td>19970330.0</td>\n",
       "      <td>2</td>\n",
       "      <td>20.76</td>\n",
       "      <td>1997-03-30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   User ID        Date  sales_count  sales_amount         Day\n",
       "0        1  19970101.0            1         11.77  1997-01-01\n",
       "1        2  19970112.0            1         12.00  1997-01-12\n",
       "2        2  19970112.0            5         77.00  1997-01-12\n",
       "3        3  19970102.0            2         20.76  1997-01-02\n",
       "4        3  19970330.0            2         20.76  1997-03-30"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User ID</th>\n",
       "      <th>tradeTimes</th>\n",
       "      <th>totalAmount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>11.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>89.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>156.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>100.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>11</td>\n",
       "      <td>385.61</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   User ID  tradeTimes  totalAmount\n",
       "0        1           1        11.77\n",
       "1        2           2        89.00\n",
       "2        3           6       156.46\n",
       "3        4           4       100.50\n",
       "4        5          11       385.61"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "m = data.groupby(['User ID'])['sales_amount'].sum().reset_index(name=\"totalAmount\")\n",
    "\n",
    "# f表和r表中的sales_count联结合并，以计算消费额\n",
    "f_total = pd.merge(f,m,on='User ID',how='inner') #基准是ID\n",
    "\n",
    "f_total.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# RFM总表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User ID</th>\n",
       "      <th>Day</th>\n",
       "      <th>recentDay</th>\n",
       "      <th>tradeTimes</th>\n",
       "      <th>totalAmount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1997-01-01</td>\n",
       "      <td>546</td>\n",
       "      <td>1</td>\n",
       "      <td>11.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1997-01-12</td>\n",
       "      <td>535</td>\n",
       "      <td>2</td>\n",
       "      <td>89.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>1998-05-28</td>\n",
       "      <td>34</td>\n",
       "      <td>6</td>\n",
       "      <td>156.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>1997-12-12</td>\n",
       "      <td>201</td>\n",
       "      <td>4</td>\n",
       "      <td>100.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>1998-01-03</td>\n",
       "      <td>179</td>\n",
       "      <td>11</td>\n",
       "      <td>385.61</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   User ID        Day  recentDay  tradeTimes  totalAmount\n",
       "0        1 1997-01-01        546           1        11.77\n",
       "1        2 1997-01-12        535           2        89.00\n",
       "2        3 1998-05-28         34           6       156.46\n",
       "3        4 1997-12-12        201           4       100.50\n",
       "4        5 1998-01-03        179          11       385.61"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Total = pd.merge(r,f_total,on='User ID',how='inner')\n",
    "\n",
    "Total.head()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 导出为Excel 用于后续画图"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "Total.to_excel(r'RFM.xlsx', index = False)"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "b67aaaf81ae69abf69716016222628bbccecfe74f49059f506d65fa8431bddca"
  },
  "kernelspec": {
   "display_name": "Python 3.9.5 64-bit",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.5"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
